

* Please change the “path” to the working directory to run the code

global path "data"
global path_raw "data/raw"
global path_analysis "data/analysis_data"
global path_output "data/output_tables_figures"

/*----------------------------------------------------------------------------*/

// OpenFEMA NFIP Data //

* The raw data is publicly available (see README)
* Becasue of the data's large size, the replication package only includes a pseudo dataset
* The above code produces the analysis NFIP dataset: the number of flood insurance policies in-force at the ctn-year level, for all policies and SFHA policies, respectively
* In the replication package, the actual analysis data "policies_ctn_year_analysis.dta" is included


use "$path_raw/raw_nfip.dta", clear // Pseudo data "$path_raw/raw_nfip_pseudo.dta" is provided in /data/raw/
	
gen SFHA=0

replace SFHA=1 if flood_zone=="A"
replace SFHA=1 if flood_zone=="AO"
replace SFHA=1 if flood_zone=="AH"
replace SFHA=1 if flood_zone=="AHB"

forvalues i=1/9 {
	replace SFHA=1 if flood_zone=="A0`i'"
	replace SFHA=1 if flood_zone=="V0`i'"
}
forvalues i=10/30 {
	replace SFHA=1 if flood_zone=="A`i'"
	replace SFHA=1 if flood_zone=="V`i'"
}

replace SFHA=1 if flood_zone=="A99"
replace SFHA=1 if flood_zone=="AE"
replace SFHA=1 if flood_zone=="AR"
replace SFHA=1 if flood_zone=="VO"
replace SFHA=1 if flood_zone=="VE"
replace SFHA=1 if flood_zone=="V"

gen state_code=substr(FIPS,1,2)
drop if state_code=="60" | state_code=="66" | state_code=="69" | state_code=="72" | state_code=="78"

drop if ctn==""

replace ctn=FIPS+ctn
replace ctn=subinstr(ctn,".","",.)

sort ctn

save "$path/temp_nfip.dta", replace
	
/*----------------------------------------------------------------------------*/

forvalues y=2010/2018 {
	
	use "$path/temp_nfip.dta", clear
	
	gen time=ym(`y', 12) 
	format time %tm

	gen x=(date_policystart<=time & time<date_policyend)
	
	replace policycount=0 if x==0
	
	preserve

	by ctn: egen policy=sum(policycount)

	by ctn: keep if _n==1
	keep FIPS ctn time policy

	save "$path/temp_policies_`y'.dta", replace
	
	restore
	
	replace policycount=0 if SFHA==0 // only count the SFHA policies

	by ctn: egen policy_SFHA=sum(policycount)

	by ctn: keep if _n==1
	keep FIPS ctn time policy_SFHA

	save "$path/temp_SFHA_policies_`y'.dta", replace
}
/*----------------------------------------------------------------------------*/
clear
forvalues y=2010/2018 {
	append using "$path/temp_policies_`y'.dta"
	erase "$path/temp_policies_`y'.dta"
}
sort ctn time
gen year=year(dofm(time))
order ctn time year

save "$path/temp_policies_ctn_year.dta", replace

/*----------------------------------------------------------------------------*/

clear
forvalues y=2010/2018 {
	append using "$path/temp_SFHA_policies_`y'.dta"
	erase "$path/temp_SFHA_policies_`y'.dta"
}
sort ctn time
gen year=year(dofm(time))
order ctn time year

save "$path/temp_SFHA_policies_ctn_year.dta", replace
/*----------------------------------------------------------------------------*/

use "$path/temp_policies_ctn_year.dta", clear
merge 1:1 ctn year using "$path/temp_SFHA_policies_ctn_year.dta"
drop _merge

erase "$path/temp_SFHA_policies_ctn_year.dta"
erase "$path/temp_policies_ctn_year.dta"

save "$path_analysis/policies_ctn_year_analysis.dta", replace 

* In the replication package, the actual analysis data "policies_ctn_year_analysis.dta" is included in /data/analysis_data/

erase "$path/temp_nfip.dta"


***************************************************************************************************************************************************

// Define treatment group (see detail in the manuscript) //

use "$path_analysis/policies_ctn_year_analysis.dta", clear

keep if year<2012
gen x=policy_SFHA/policy
bysort ctn: egen SFHA_frac=mean(x)
drop x
bysort ctn: keep if _n==1

egen p50 = pctile(SFHA_frac), p(50)

gen treatment=.
replace treatment=1 if SFHA_frac>p50 & SFHA_frac!=.
replace treatment=0 if SFHA_frac<=p50 & SFHA_frac!=.
drop if treatment==.

keep ctn FIPS treatment SFHA_frac

save "$path_analysis/treatment_dummy.dta", replace

***************************************************************************************************************************************************

// OpenFEMA NFIP Data - Construction Year //

* The raw data is publicly available (see README)
* Becasue of the data's large size, the replication package only includes a pseudo dataset
* The above code produces the analysis dataset for Table 12: the average construction year at the ctn level
* In the replication package, the actual analysis data "ctn_construction_year_analysis.dta" is included

use "$path_raw/raw_nfip.dta", clear // Pseudo data "$path_raw/raw_nfip_pseudo.dta" is provided in /data/raw/

gen year = floor(date_policystart/12) + 1960
gen construction_year = floor(date_construction/12) + 1960

drop if ctn==""
replace ctn=FIPS+ctn
replace ctn=subinstr(ctn,".","",.)

keep ctn year construction_year

sort ctn year construction_year 

by ctn year: egen cyear=mean(construction_year)
by ctn year: keep if _n==1
drop construction_year

by ctn: egen x=mean(cyear)
by ctn: keep if _n==1

rename x construction_year
keep ctn construction_year

replace construction_year=round(construction_year,1)

save "$path_analysis/ctn_construction_year_analysis.dta", replace 

* In the replication package, the actual analysis data "ctn_construction_year_analysis.dta" is included in /data/analysis_data/

***************************************************************************************************************************************************

// Community Status Book - Defining ctn-level FIRM year (see details in the manuscript) //
* For the analysis in Table 12

use "$path_raw/raw_community_status_book.dta", clear // Pseudo data "$path_raw/raw_community_status_book_pseudo.dta" is provided in /data/raw/

gen year=Regular_Program_Eligibility_Date
split year, p(/)
replace year=year3
drop year1 year2 year3
destring year, replace
replace year=2000+year if year<=27 & year>=0
replace year=1900+year if year>=60 & year<=99
rename year year_eligibility

gen year=Regular_Program_Entry_Date
split year, p(/)
replace year=year3
drop year1 year2 year3
replace year=subinstr(year,"(S)","",.)
replace year=subinstr(year,"(E)","",.)
replace year=subinstr(year,"(W)","",.)
destring year, replace
replace year=2000+year if year<=27 & year>=0
replace year=1900+year if year>=60 & year<=99
rename year year_entry

replace year_entry=year_eligibility if ustrpos(Regular_Program_Entry_Date ,"(") & year_entry>year_eligibility

gen FIRM_time=min(year_entry, year_eligibility)

sort FIPS FIRM_time CID
order FIPS FIRM_time CID
by FIPS: gen tot=_N
by FIPS FIRM_time: gen toty=_N
by FIPS FIRM_time: keep if _n==1
drop CID
gen frac=toty/tot

by FIPS: egen max=max(frac)

replace FIRM_time=. if frac!=max
by FIPS: egen FIRM_most=max(FIRM_time)

by FIPS: keep if _n==1

keep FIPS FIRM_most
rename FIRM_most FIRM_year

save "$path/temp_FIRM_year_county.dta", replace
/*----------------------------------------------------------------------------*/

* get ctn-county crosswalk

use "$path_raw/raw_nfip.dta", clear // Pseudo data "$path_raw/raw_nfip_pseudo.dta" is provided in /data/raw/
drop if ctn==""
replace ctn=FIPS+ctn
replace ctn=subinstr(ctn,".","",.)
keep FIPS ctn
bysort FIPS ctn: keep if _n==1
save "$path/temp_county_ctn_crosswalk.dta", replace

use "$path/temp_FIRM_year_county.dta", clear
merge 1:m FIPS using "$path/temp_county_ctn_crosswalk.dta"
keep if _merge==3
drop _merge
sort FIPS ctn
save "$path_analysis/FIRM_year_analysis.dta", replace

* In the replication package, the actual analysis data "FIRM_year_analysis.dta" is included in /data/analysis_data/

erase "$path/temp_FIRM_year_county.dta"
erase "$path/temp_county_ctn_crosswalk.dta"

***************************************************************************************************************************************************


// OpenFEMA NFIP Data - Monthly-level Number of policies (Figure 1) //

* The raw data is publicly available (see README)
* Becasue of the data's large size, the replication package only includes a pseudo dataset
* The above code produces the analysis NFIP dataset: the number of flood insurance policies in-force at the ctn-year-month level, for all policies and SFHA policies, respectively
* In the replication package, the actual analysis data "policies_ctn_monthly_analysis.dta" is included

use "$path_raw/raw_nfip.dta", clear // Pseudo data "$path_raw/raw_nfip_pseudo.dta" is provided in /data/raw/
		
gen SFHA=0

replace SFHA=1 if flood_zone=="A"
replace SFHA=1 if flood_zone=="AO"
replace SFHA=1 if flood_zone=="AH"
replace SFHA=1 if flood_zone=="AHB"

forvalues i=1/9 {
	replace SFHA=1 if flood_zone=="A0`i'"
	replace SFHA=1 if flood_zone=="V0`i'"
}
forvalues i=10/30 {
	replace SFHA=1 if flood_zone=="A`i'"
	replace SFHA=1 if flood_zone=="V`i'"
}

replace SFHA=1 if flood_zone=="A99"
replace SFHA=1 if flood_zone=="AE"
replace SFHA=1 if flood_zone=="AR"
replace SFHA=1 if flood_zone=="VO"
replace SFHA=1 if flood_zone=="VE"
replace SFHA=1 if flood_zone=="V"

gen state_code=substr(FIPS,1,2)
drop if state_code=="60" | state_code=="66" | state_code=="69" | state_code=="72" | state_code=="78"

drop if ctn==""

replace ctn=FIPS+ctn
replace ctn=subinstr(ctn,".","",.)
	
sort ctn
	
save "$path/temp_nfip.dta", replace

/*----------------------------------------------------------------------------*/

forvalues y=2010/2018 {
	forvalues m=1/12 {
	
		use "$path/temp_nfip.dta", clear
		
		gen time=ym(`y', `m') 
		format time %tm

		gen x=(date_policystart<=time & time<date_policyend)

		replace policycount=0 if x==0
		
		preserve

		by ctn: egen policy=sum(policycount)

		by ctn: keep if _n==1
		keep FIPS ctn time policy

		save "$path/temp_policies_`y'_`m'.dta", replace
		
		restore
		
		replace policycount=0 if SFHA==0 // only count the SFHA policies

		by ctn: egen policy_SFHA=sum(policycount)

		by ctn: keep if _n==1
		keep FIPS ctn time policy_SFHA

		save "$path/temp_SFHA_policies_`y'_`m'.dta", replace
		
	}
}

clear
forvalues y=2010/2018 {
	forvalues m=1/12 {
		append using "$path/temp_policies_`y'_`m'.dta"
		erase "$path/temp_policies_`y'_`m'.dta"
	}
}
sort ctn time
gen year=year(dofm(time))
gen month=month(dofm(time))
order ctn time year month

save "$path/temp_policies_ctn_monthly.dta", replace

/*----------------------------------------------------------------------------*/

clear
forvalues y=2010/2018 {
	forvalues m=1/12 {
		append using "$path/temp_SFHA_policies_`y'_`m'.dta"
		erase "$path/temp_SFHA_policies_`y'_`m'.dta"
	}
}
sort ctn time
gen year=year(dofm(time))
order ctn time year

save "$path/temp_SFHA_policies_ctn_monthly.dta", replace
/*----------------------------------------------------------------------------*/

use "$path/temp_policies_ctn_monthly.dta", clear
merge 1:1 ctn time using "$path/temp_SFHA_policies_ctn_monthly.dta"
drop _merge

erase "$path/temp_SFHA_policies_ctn_monthly.dta"
erase "$path/temp_policies_ctn_monthly.dta"

save "$path_analysis/policies_ctn_monthly_analysis.dta", replace 

* In the replication package, the actual analysis data "policies_ctn_monthly_analysis.dta" is included in /data/analysis_data/


***************************************************************************************************************************************************


// OpenFEMA NFIP Data - Monthly-level Premium (Figure 1) //

* The raw data is publicly available (see README)
* Becasue of the data's large size, the replication package only includes a pseudo dataset
* The above code produces the analysis NFIP dataset: the average premium at the ctn-year-month level, for all policies and SFHA policies, respectively
* In the replication package, the actual analysis data "premium_ctn_monthly_analysis.dta" is included

// Premium in SFHA

forvalues y=2010/2018 {
	forvalues m=1/12 {
		use "$path/temp_nfip.dta", clear 

		gen year=`y'
		gen month=`m'
		gen time=ym(year, month) 
		format time %tm
		drop year month

		gen x=(date_policystart<=time & time<date_policyend)
		
		preserve
		
		keep if SFHA==1
		
		replace policycount=0 if x==0
		replace premium=0 if x==0

		by ctn: egen policy_SFHA=sum(policycount)
		by ctn: egen premium_SFHA=sum(premium)
		by ctn: keep if _n==1

		gen premium_avg_SFHA=premium_SFHA/policy_SFHA
		keep FIPS ctn time policy_SFHA premium_avg_SFHA

		save "$path/temp_premium_SFHA_`y'_`m'.dta", replace
		
		restore
		
		keep if SFHA==0
		
		replace policycount=0 if x==0
		replace premium=0 if x==0

		by ctn: egen policy_nonSFHA=sum(policycount)
		by ctn: egen premium_nonSFHA=sum(premium)
		by ctn: keep if _n==1

		gen premium_avg_nonSFHA=premium_nonSFHA/policy_nonSFHA
		keep FIPS ctn time policy_nonSFHA premium_avg_nonSFHA

		save "$path/temp_premium_nonSFHA_`y'_`m'.dta", replace
		
	}
}
/*----------------------------------------------------------------------------*/

clear
forvalues y=2010/2018 {
	forvalues m=1/12 {
		append using "$path/temp_premium_SFHA_`y'_`m'.dta"
		erase "$path/temp_premium_SFHA_`y'_`m'.dta"
	}
}
sort ctn time

gen temp=dofm(time)
format temp %d
gen year=year(temp)
gen month=month(temp)
drop temp
order ctn time year month

save "$path_analysis/premium_SFHA_ctn_monthly_analysis.dta", replace

* In the replication package, the actual analysis data "premium_SFHA_ctn_monthly_analysis.dta" is included in /data/analysis_data/

/*----------------------------------------------------------------------------*/

clear
forvalues y=2010/2018 {
	forvalues m=1/12 {
		append using "$path/temp_premium_nonSFHA_`y'_`m'.dta"
		erase "$path/temp_premium_nonSFHA_`y'_`m'.dta"
	}
}
sort ctn time

gen temp=dofm(time)
format temp %d
gen year=year(temp)
gen month=month(temp)
drop temp
order ctn time year month

save "$path_analysis/premium_nonSFHA_ctn_monthly_analysis.dta", replace

* In the replication package, the actual analysis data "premium_nonSFHA_ctn_monthly_analysis.dta" is included in /data/analysis_data/

erase "$path/temp_nfip.dta"

/*----------------------------------------------------------------------------*/








